<?php
namespace Api\Model\Amazon;

use Think\Exception;

class ReportSaleModel extends CommonModel{
    // 数据表前缀
    protected $tablePrefix   = 'api_';
    // 数据库配置
    protected $connection    = 'fbawarehouse';
    // 数据表名（不包含表前缀）
    protected $tableName     = 'report_sale';

    protected $trueTableName = 'api_report_sale';

    /**
     * @param $rowLists
     * @param $tableName
     * 描述：插入解析数据
     */
    public function insertData($tableName,$rowLists){

        /*$sql = 'SELECT `create_time` FROM `' . $tableName . '` ORDER BY `id` DESC LIMIT 1';
        $maxCreateTime = $this->query($sql);
        $maxCreateTime = !empty($maxCreateTime) ? $maxCreateTime[0]['create_time'] : '';
        if(!empty($maxCreateTime) && ((time() - strtotime($maxCreateTime)) > 86400 )) {
            $sql = 'TRUNCATE TABLE  `' . $tableName . '`';
            $this->query($sql);
        }*/

        /*if(!empty($rowLists)) {
            $rowListsChunks = array_chunk($rowLists, 500);
            foreach($rowListsChunks as $rowListsChunk) {
                $this->addAll($rowListsChunk);
            }
        }*/
        if(!empty($rowLists)) {
            foreach($rowLists as $row) {
                try {
                    $this->add($row);
                } catch(Exception $ex) {
                    continue;
                }
            }
        }
    }

    /**
     * @param array $options
     * @return bool|mixed
     * 描述：根据条件查询数据
     */
    public function selectData($options = array()){
        $data = $this->where($options)->select();

        return isset($data) ? $data : false;
    }

    public function fbaSaleData($options = array()){
        $sql = 'SELECT `ars`.`account_id`,`as`.`shorthand_code`,`aa`.`name` AS accountName,`ars`.`sku`,`ars`.`asin`,
                SUM(`ars`.`quantity`)/7 AS daysale,`aass`.`private_sku`,`sst`.`item`,`aass`.`sale_status_id`
                FROM `api_report_sale` AS `ars`
                LEFT JOIN `amazonorder_accounts` AS `aa` ON `aa`.`id` = `ars`.`account_id`
                LEFT JOIN `api_account_seller_sku` AS `aass` ON `aass`.`account_id` = `ars`.`account_id` AND `aass`.`seller_sku` = `ars`.`sku`
                LEFT JOIN `amazonorder_sites` AS `as` ON `aass`.`site_id` = `as`.`id`
                LEFT JOIN `skusystem_sku_taxrate` AS `sst` ON `sst`.`sku` = `aass`.`private_sku`
                WHERE LEFT(`ars`.`purchase_date`, 10) < "' . date('Y-m-d', strtotime('-3 day')) . '"
                AND LEFT(`ars`.`purchase_date`, 10) > "' . date('Y-m-d', strtotime('-11 day')) . '"
                AND `ars`.`account_id` != 0';

        $condition = '';
        (isset($options['sku']) && $options['sku']) && $condition .= " AND `aass`.`private_sku` IN (" . $options['sku'] . ")";
        $condition .= ' GROUP BY `ars`.`account_id`,`ars`.`sku`';

        $reportDataLists = $this->query($sql.$condition);

        return !empty($reportDataLists) ? $reportDataLists : array();
    }

    /**
     * 将本天次解析报告汇总每天销量插入销量表
     */
    public function saleTodaySale ($report_file_id) {
        $sqi = 'SELECT  `rs` . `account_id` ,  `skus` . `private_sku` ,
                SUM(`rs` . `quantity`) AS  `sale` ,
                DATE_FORMAT(  `rs` . `purchase_date` ,  \'%Y-%m-%d\' ) AS  `sale_date`
                FROM  `api_report_sale` AS `rs`
                LEFT JOIN `api_account_seller_sku` AS `skus` 
                ON `skus` . `seller_sku` = `rs` . `sku` AND `rs` . `account_id` = `skus`. `account_id`
                WHERE `rs` . `report_file_id` = ' . $report_file_id . ' 
                AND  `rs` . `quantity` != 0 
                AND `rs` . `account_id` != 0 
                AND  `rs` . `order_status` !=  \'Cancelled\'';

        $reportSaleLists = $this->query(
            $sqi .
            ' GROUP BY `rs`.`account_id`,`skus`.`private_sku`,`sale_date`,SUBSTRING(TRIM(`rs`.`sales_channel`),8)'
        );

        if(!empty($reportSaleLists)) {
            D('Api/Amazon/ReportDaySale')->insertData($reportSaleLists);
        }

    }

    public function getSkuQuantity($condition) {

        $sql = "SELECT `ars`.`amazon_order_id`,`ars`.`purchase_date`,`ars`.`sku`,`ars`.`order_status`,`ars`.`quantity`,
                `ars`.`asin`,`aa`.`name`,`aass`.`private_sku`,`as`.`shorthand_code`
                FROM `api_report_sale` AS `ars`
                LEFT JOIN `amazonorder_accounts` AS `aa`
                ON `ars`.`account_id` = `aa`.`id`
                LEFT JOIN `api_account_seller_sku` AS `aass`
                ON `aass`.`account_id` = `ars`.`account_id`
                AND `aass`.`seller_sku` = `ars`.`sku`
                LEFT JOIN `amazonorder_sites` AS `as`
                ON `aass`.`site_id` = `as`.`id`
                WHERE `ars`.`sku` LIKE '%" . $condition['sku'] . "%'";

        isset($condition['account_id']) && $sql .= " AND `ars`.`account_id` = " . $condition['account_id'] ." ";
        isset($condition['purchase_date_start']) && $sql .= " AND `ars`.`purchase_date` >= '" . $condition['purchase_date_start'] . "' ";
        isset($condition['purchase_date_end']) && $sql .= " AND `ars`.`purchase_date` <= '" . $condition['purchase_date_end'] . "' ";

        $sql .= " ORDER BY `ars`.`purchase_date` DESC";
        $data = $this->query($sql);

        return !empty($data) ? $data : array();
    }

    public function getSingleData() {
        $sql = 'SELECT `ars`.`sku`,`as`.`shorthand_code`,`aass`.`private_sku`,`aa`.`name`,`ars`.`asin`,`ars`.`quantity`,
                `ars`.`item_price`,LEFT(`ars`.`purchase_date`,10) AS purchase_date,`ars`.currency FROM `api_report_sale` as `ars` 
                LEFT JOIN `amazonorder_accounts` as `aa` 
                ON `ars`.`account_id` = `aa`.`id` 
                LEFT JOIN `api_account_seller_sku` as `aass` 
                ON `ars`.`account_id` = `aass`.`account_id` AND `ars`.`sku` = `aass`.`seller_sku` 
                LEFT JOIN `amazonorder_account_troop_site` aats ON aa.id = aats.account_id
                LEFT JOIN `amazonorder_sites` as `as` ON `as`.`id` = `aats`.`site_id` 
                WHERE `ars`.`purchase_date` >= "' . date("Y-m-d 00:00:00",strtotime("-33 day")) .'" 
                AND `ars`.`purchase_date` <= "' . date("Y-m-d 23:59:59",strtotime("-3 day")) .'" 
                AND `ars`.`sku` NOT REGEXP "^[A-Z][0-9][A-Z][0-9]" 
                AND `ars`.`sku` NOT LIKE "SHUADAN%"';
        echo $sql;exit;
        $data = $this->query($sql);

        $title = array(
            'sku' => 'SellerSKU',
            'shorthand_code' => '站点',
            'private_sku' => '公司SKU',
            'name' => '账号名',
            'asin' => 'ASIN',
            'quantity' => '销量',
            'item_price' => '单价',
            'purchase_date' => '下单时间',
            'currency' => '币种'

        );
        $result['data'] = !empty($data) ? $data : array();
        $result['title'] = $title;
        $result['fileName'] = "30天销量明细报表";
        return $result;
    }
}